set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_whole_effect_deliver_overdue_dt 
select
      wide.waybill_no
     ,wide.sign_date                   as sign_date                --签收日期
     ,wide.sign_network_code           as sign_network_code        --签收网点编码
     ,wide.sign_network_name           as sign_network_name        --签收网点名称
     ,wide.sign_fran_code              as sign_fran_code           --签收加盟商code
     ,wide.sign_fran_name              as sign_fran_name           --签收加盟商名称
     ,wide.sign_agent_code             as sign_agent_code          --签收代理区code
     ,wide.sign_agent_name             as sign_agent_name          --签收代理区名称
     ,wide.sign_regional_id            as sign_regional_id         --签收大区id
     ,wide.sign_regional_desc          as sign_regional_desc       --签收大区名称
     ,wide.sign_area_id                as sign_area_id             --签收区县id
     ,wide.sign_area_desc              as sign_area_desc           --签收区县名称
     ,wide.sign_city_id                as sign_city_id             --签收城市id
     ,wide.sign_city_desc              as sign_city_desc           --签收城市名称
     ,wide.sign_provider_id            as sign_provider_id         --签收省份id
     ,wide.sign_provider_desc          as sign_provider_desc       --签收省份名称
     ,wide.ordersource_code            as ordersource_code         --订单来源编码
     ,wide.ordersource_name            as ordersource_name         --订单来源名称
     ,wide.deliver_user_code           as deliver_user_code        --出仓员编码
     ,wide.deliver_user_name           as deliver_user_name        --出仓员名称                                    
     ,wide.end_site_arrival_later_time as last_arrival_scan_time   --末端网点到件时间
     ,wide.deliver_scan_time           as deliver_scan_time        --出仓时间
     ,wide.instore_scan_time           as instore_scan_time        --入库时间
     ,wide.outstore_scan_time          as outstore_scan_time       --出库时间
     ,wide.aging_sign_scan_time        as aging_sign_scan_time     --时效签收时间
     ,wide.actual_sign_scan_time       as actual_sign_scan_time    --实际签收时间
     ,round(wide.end_network_arrival_deliver_difftime ,2) as end_network_arrival_deliver_time --末端网点到件-派件时长
     ,round(wide.deliver_aging_sign_difftime          ,2) as deliver_aging_sign_time          --派件-时效签收时长
     ,round(wide.real_pre_sign_difftime               ,2) as actual_aging_sign_time           --时效签收-实际签收时长
     ,wide.if_reback                   as if_reback      --是否退转件,1:是,0:否
     ,wide.if_end_piece                as if_end_piece   --是否完结件,1:是,0:否
     ,wide.if_intercept                as if_intercept   --是否拦截件,1:是,0:否
     ,wide.if_deliver_township         as if_deliver_township --是否乡镇件
     ,wide.if_after_deliver            as if_after_deliver    --是否稍后出仓
     ,wide.after_deliver_time          as after_deliver_time  --稍后出仓时间
     ,wide.sign_date as dt --签收日期
from jms_dws.dws_wide_unsign_whole_effect_dt wide
where wide.dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
  and wide.send_network_code is not null
  and wide.sign_date between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
distribute by sign_date,pmod(hash(rand()),5)
;